#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
   @Author: 即将拥有人鱼线的yjw
   @Time: 2023/10/16 17:15
   @File: suspicious_trade.py
   @desc:可疑交易测试
"""
import re

import pandas  as pd
from prettytable import PrettyTable
from tabulate import tabulate

from DataTest.databases import MysqlUtil, PostgreSqlUtil, OracleUtil


def fomat_print(list:list):
    x = pd.DataFrame(list)
    print(tabulate(x, headers='keys', tablefmt='psql'))

def change_parameter(ruleNo,sql_text):
    sql = f"select parameter_no from hsamlls.amlls_cfg_ruleparameter  where rules_no = '{ruleNo}'"
    getParameterNoList = db.execsql(sql)
    # fomat_print(getParameterNoList)
    if getParameterNoList:
        for i in getParameterNoList:
            i = keys_to_lowercase(i)
            parameter_no = i['parameter_no']
            sql = f"select parameter_name,parameter_value from hsamlls.amlls_cfg_ruleparameter  where rules_no = '{ruleNo}' and parameter_no ='{parameter_no}'"
            # print(sql)
            valueList = db.execsql(sql)
            result = keys_to_lowercase(valueList[0])
            parameter_name = result['parameter_name']
            parameter_value= result['parameter_value']
            txt_a = '#{' + parameter_no + '}'
            txt_b = '${' + parameter_no + '}'
            # print(txt_a)
            if parameter_value:

                if 'WorkDate' in parameter_name or ('TradeBalance' in parameter_name) or ('YearLimit' in parameter_name) or ('Count' in parameter_name):

                    sql_text= sql_text.replace(txt_a,parameter_value)
                    sql_text= sql_text.replace(txt_b, parameter_value)
                else:
                    replace_value = "'"+parameter_value+"'"
                    sql_text= sql_text.replace(txt_a, replace_value)
                    sql_text= sql_text.replace(txt_b, replace_value)

            else:
                sql_text= sql_text.replace(txt_a, '')
                sql_text= sql_text.replace(txt_b, '')

    sql_text = sql_text.replace('#{ExchangeDate(occurDate,0)}',str(occurDate))
    sql_text = sql_text.replace('#{bBeginDate}',str(BeginDate))
    sql_text = sql_text.replace('#{bEndDate}',str(EndDate))

    # print(sql_text)
    return sql_text



def suspicious_trade(ruleNo,ruleVersion):


    sql = f"select step_no  from  hsamlls.amlls_cfg_rulesql  where rules_no = '{ruleNo}' order by step_no"
    print(sql)
    getRuleStepNo = db.execsql(sql)
    print(getRuleStepNo)
    for i in getRuleStepNo:
        i = keys_to_lowercase(i)
        step_no = i['step_no']
        print(f"\n")
        print(f"*************执行第{step_no}步******************")
        sql = f"select  sql_text ,table_name,idx_create_sql ,table_create_sql   from  hsamlls.amlls_cfg_rulesql  where rules_no = '{ruleNo}' and step_no = '{step_no}' and rule_version ='{ruleVersion}' "
        getSql = db.execsql(sql)
        result = keys_to_lowercase(getSql[0])
        sql_text = result['sql_text']
        table_name = result['table_name']
        idx_create_sql = result['idx_create_sql']
        table_create_sql = result['table_create_sql']
        re.findall(r'[^\r\n]+', sql_text)
        sql_insert = change_parameter(ruleNo,sql_text)

        if table_create_sql.strip():
            re.findall(r'[^\r\n]+', table_create_sql)
            # print(table_create_sql)
            list = table_create_sql.split('`')
            table = list[1]
            # print(table)
            drop_sql = f'drop table  hsrcce.`{table}`'
            # print(drop_sql)
            # db.execsql(drop_sql)
            # db.execsql(table_create_sql.replace('`temp_','hsrcce.`temp_'))
            if idx_create_sql.strip():
                idx_create_sql.replace('temp_','hsrcce.temp_')
                # db.execsql(idx_create_sql)
        if '.'in table_name:
            print(sql_insert)
            result = db.execsql(sql_insert.replace(" temp_", "  hsrcce.temp_"))
            print(f"*************第{step_no}步执行结果如下******************")
            if result:
                 fomat_print(result)
            else:
                print('=====无数据返回=====')
            # insertSql = "insert into "+table_name+" "+ sql_insert
            # # print(insertSql)
            # if step_no < len(getRuleStepNo):
            #      db.execsql(insertSql.replace(" temp_", "  hsrcce.temp_"))
        else:
            print(sql_insert)
            result = db.execsql(sql_insert.replace(" temp_", "  hsrcce.temp_"))
            print(f"*************第{step_no}步执行结果如下******************")
            # if result:
            #     fomat_print(result)
            # else:
            #     print('=====无数据返回=====')
            insertSql = "insert into hsrcce."+table_name+" values "+ sql_insert
            # print(insertSql)
            # db.execsql(insertSql.replace(" temp_", "  hsrcce.temp_"))



def keys_to_lowercase(input_dict):
    """将字典中的所有键转换为小写"""
    return {key.lower(): value for key, value in input_dict.items()}

if __name__ == "__main__":
    # db = MysqlUtil('root', 'fxhg@1234', '10.20.181.116', port=33061)
    # db = MysqlUtil('root', 'root@123', '10.20.46.217', port=33061)
    db = OracleUtil('bsaml','bsaml','10.20.37.111',server='bsaml')
    # db = PostgreSqlUtil('postgresql','fxq123','10.20.37.112',server='hsrcm')

    occurDate = 20240308
    BeginDate = 20240308
    EndDate = 20240321
    ruleNo = 'D048'
    rule_version = '1010'
    # change_parameter(ruleNo,sql_text)
    suspicious_trade(ruleNo,rule_version)

    # testUnit = "DATE_FORMAT(date_sub(min(a.apply_date), interval #{D00807} day),'%Y%m%d') as fistdate"
    # testUnit = testUnit.replace("#{D00807}",'10')
    # print(testUnit)